using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VICC_STOCKCHG
    {
        VICC_QUERYTB QTB = new VICC_QUERYTB();

        public void InsertICC_STOCKCHG(DataRow dwData, int option = 1)
        {


            switch (option)
            {
                case 1:
                    GBSUMSTKCHG(dwData);
                    break;
                case 2:
                    FBSUMSTKCHG(dwData);
                    break;

            }

        }
        public void GBSUMSTKCHG(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "INSERT INTO ICC_STOCKCHG (MANDANT,LO,LB,MDATE,SPPART,ZWPART,FBPART,BEM1,WAKZ,ART,VAR,FARBE,DESS,COLORIT,FINISH,FSIZE,BS,BSDES,BSQTY,UNS) "
            + " SELECT * FROM (SELECT GABMANDANT AS MANDANT,GABLO AS LO,GABLB AS LB,GABBUCHDAT AS MDATE, GPSPPART AS SPPART,GPZWPART AS ZWPART,GPFBPART AS FBPART,GPBEM1 AS BEM1,'G' AS WAKZ, GABART AS ART,GABVAR AS VAR,GABFARBE AS FARBE,'          ' AS FINISH,'          ' AS DESS,'          ' AS COLORIT,'       ' AS FSIZE, W.GABBS AS BS,MAX(SCN) AS BSDES,SUM(GABKG * 0.001) AS BSQTY, COUNT(DISTINCT(GABLBEHI)) AS UNS "
            + " FROM ICC_GABEW W JOIN GPDAT ON GABPARTIE = GPPARTIE JOIN ICC_GABBS B ON W.GABBS = B.GABBS WHERE W.GABBS NOT IN ('61', '62') "
            + " GROUP BY GABMANDANT,GABLO, GABLB,GABBUCHDAT,GPSPPART,GPZWPART,GPFBPART, GABART,GABVAR,GABFARBE,GPBEM1,W.GABBS ORDER BY 1, 2, 3, 4, 5, 6, 7) X "
            + " WHERE NOT EXISTS (SELECT 1 FROM ICC_STOCKCHG Y WHERE X.MANDANT = Y.MANDANT AND X.LO = Y.LO AND X.LB = Y.LB AND X.MDATE = Y.MDATE AND X.FBPART = Y.FBPART AND X.BEM1 = Y.BEM1 AND X.SPPART = Y.SPPART AND X.ZWPART = Y.ZWPART AND X.ART = Y.ART AND X.VAR = Y.VAR AND X.FARBE = Y.FARBE AND X.FINISH = Y.FINISH AND X.BS = Y.BS)";
            Connect2Proxy(or1);
        }
        public void FBSUMSTKCHG(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "INSERT INTO ICC_STOCKCHG(MANDANT,WAKZ,LO,LB,MDATE,SPPART,ZWPART,FBPART,BEM1,ART,VAR,FARBE,DESS,COLORIT,FINISH,FSIZE,BS,BSDES,BSQTY) "
            + " SELECT * FROM (SELECT WBEWMANDANT AS MANDANT,WBEWWAKZ AS WAKZ,WBEWLO AS LO,WBEWLB AS LB,WBEWBUCHDAT AS MDATE,'          ' as SPPART,'          ' as ZWPART,'          ' as FBPART,'                                        ' as BEM1,WBEWART AS ART,WBEWVAR AS VAR,WBEWFARBE AS FARBE,WBEWDESS AS DESS, WBEWCOLORIT AS COLORIT,WBEWBESCHICH AS FINISH,'       ' AS FSIZE, W.WBEWBS AS BS,MAX(SCN) AS BSDES,SUM(WBEWMTR * 0.001) AS BSQTY"
            + " FROM ICC_WBBEW W JOIN WBDAT ON WBEWMANDANT=WBMANDANT AND WBRWKRS=WBEWRWKRS AND WBRWLFD=WBEWRWLFD JOIN ICC_GABBS B ON W.WBEWBS = B.GABBS WHERE W.WBEWBS NOT IN ('61', '62') "
            + " GROUP BY WBEWMANDANT,WBEWWAKZ,WBEWLO, WBEWLB,WBEWBUCHDAT, WBEWART,WBEWVAR,WBEWFARBE,WBEWDESS,WBEWCOLORIT,WBEWBESCHICH,W.WBEWBS ORDER BY 1, 2, 3, 4, 5, 6, 7) X "
            + " WHERE NOT EXISTS (SELECT 1 FROM ICC_STOCKCHG Y WHERE X.MANDANT = Y.MANDANT AND X.LO = Y.LO AND X.WAKZ=Y.WAKZ AND X.LB = Y.LB AND X.MDATE = Y.MDATE AND X.ART = Y.ART AND X.VAR = Y.VAR AND X.FARBE = Y.FARBE AND X.FINISH = Y.FINISH AND X.DESS = Y.DESS AND X.COLORIT = Y.COLORIT AND X.BS = Y.BS)";
            Connect2Proxy(or1);
        }
        public void DeleteICC_STOCKCHG(DataRow dwData, int option = 1)
        {
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    DSTKG01(dwData);
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
        public void DSTKG01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM ICC_STOCKCHG"
         + " WHERE GABBELEG<=:GABBELEG";
            or1.Parameters.Add("GABBELEG", OracleType.NVarChar).Value = dwData["GABBELEG"].ToString();
            Connect2Proxy(or1);
        }
        public void DSTKG02(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM ICC_STOCKCHG"
                 + " WHERE MDATE=:MDATE: AND WAKZ=:WAKZ: AND LO=:LO: AND LB=:LB:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void DSTKCHGMDATE(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM ICC_STOCKCHG"
                 + " WHERE MDATE=:MDATE: AND WAKZ=:WAKZ:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public DataSet QueryICC_STOCKCHG(DataRow dwData, int option = 1)
        {


            switch (option)
            {
                case 1:
                    SResult = QSTKCHG01(dwData);
                    break;
                case 2:
                    SResult = QSTKCHG02(dwData);
                    break;
                case 3:
                    SResult = QSTKCHG03(dwData);
                    break;
                default:
                    break;
            }


            return SResult;
        }
        public DataSet QSTKCHG01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT MAX(GABBELEG) AS GABBELEG  FROM ICC_STOCKCHG";
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QSTKCHG02(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT MAX(GABBELEG) AS GABBELEG  FROM ICC_STOCKCHG WHERE GABBUCHDAT<=:GABBUCHDAT"; ;
            or1.Parameters.Add("GABBUCHDAT", OracleType.NVarChar).Value = dwData["GABBUCHDAT"].ToString();
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QSTKCHG03(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT *  FROM ICC_STOCKCHG WHERE MANDANT=:MANDANT:  "
                + dwData["BSDES"].ToString()
                + " AND LO=:LO:  AND LB=:LB:  AND WAKZ=:WAKZ:  AND ART=:ART:  AND VAR=:VAR:  AND FARBE=:FARBE:  AND FINISH=:FINISH:  AND DESS=:DESS:"
                        + " AND COLORIT=:COLORIT: AND FSIZE=:FSIZE: AND FBPART=:FBPART:  AND SPPART=:SPPART:   AND ZWPART=:ZWPART:   AND BEM1=:BEM1:  "
                        + " ORDER BY MDATE,LO,LB,ART,VAR,FARBE,FINISH";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QSTKCHG04(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT *  FROM ICC_STOCKCHG WHERE MANDANT=:MANDANT:  "
                + dwData["BSDES"].ToString()
                + " AND LO=:LO:  AND LB=:LB:  AND WAKZ=:WAKZ:  AND ART=:ART:  AND VAR=:VAR:  AND FARBE=:FARBE:  AND FINISH=:FINISH:  AND DESS=:DESS:"
                        + " AND COLORIT=:COLORIT: AND FSIZE=:FSIZE: AND FBPART=:FBPART:  AND SPPART=:SPPART:   AND ZWPART=:ZWPART:"
                        + " ORDER BY MDATE,LO,LB,ART,VAR,FARBE,FINISH";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public void UpdateICC_STOCKCHG(DataRow dwData, string ITXDBVERSION = "50.24")
        {

            or1 = OracleDB.CreateCommand();
            #region====Command====
            or1.CommandText = "UPDATE ICC_STOCKCHG SET  GABBS=:GABBS, EN=:EN, SCN=:SCN "
                + " WHERE    ROWNID=:ROWNID ";
            #endregion
            #region====Parameters====
            or1.Parameters.Add("ROWNID", OracleType.NVarChar).Value = dwData["ROWNID"].ToString();
            or1.Parameters.Add("GABBS", OracleType.NVarChar).Value = dwData["GABBS"].ToString();
            or1.Parameters.Add("EN", OracleType.NVarChar).Value = dwData["EN"].ToString();
            or1.Parameters.Add("SCN", OracleType.NVarChar).Value = dwData["SCN"].ToString();
            #endregion
            Connect2Proxy(or1);
        }
    }
}
